<?php
/* 
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
function doQueryListe1eresVisitesPasSaisies($conn,$formation,$yearRef=null)
{
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString="select '11ereVisiteEntreprise' as etape,
                          concat (concat(tut.prenom,' '),tut.nom) as nomTut,
                          tut.mail as tutMail,
                          concat(concat(etud.nom,' '),etud.prenom) as nomEtud,
                          etud.mail as etudMail,
                          eg.groupeRef as niveau
                  from contrat inner join etudiant etud on etudCle=contrat.etudRef
                       inner join membre tut on profCle=tuteurRef
                       inner join etudiant_groupe eg on eg.etudRef=contrat.etudRef and annee=anneeCle
                       inner join groupe groupe on groupeRef=groupeCle
                  where alternanceCle not in (select alternanceRef from etapevisite1
                                              where dateRencontre is not NULL) and
                        '".$formation."' like concat('%',formationRef,'%')
                        and annee=$yearRef
               order by nomTut,niveau,nomEtud";

    $result=mysql_query($queryString,$conn);
    if (mysql_errno()!=0) error_log( "querying ",$queryString," - ",mysql_error());
    return $result;
}

function doQueryListe2emesVisitesPasSaisies($conn,$formation,$yearRef=null)
{
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString="select '12ereVisiteEntreprise' as etape,
                          concat (concat(tut.prenom,' '),tut.nom) as nomTut,
                          tut.mail as tutMail,
                          concat(concat(etud.nom,' '),etud.prenom) as nomEtud,
                          etud.mail as etudMail,
                          eg.groupeRef as niveau
                  from contrat inner join etudiant etud on etudCle=contrat.etudRef
                       inner join membre tut on profCle=tuteurRef
                       inner join etudiant_groupe eg on eg.etudRef=contrat.etudRef and annee=anneeCle
                       inner join groupe groupe on groupeRef=groupeCle
                  where alternanceCle not in (select alternanceRef from etapevisite2
                                              where dateRencontre is not NULL) and
                        '".$formation."' like concat('%',formationRef,'%')
                        and annee=$yearRef
               order by nomTut,niveau,nomEtud";

    $result=mysql_query($queryString,$conn);
    if (mysql_errno()!=0) error_log( "querying ",$queryString," - ",mysql_error());
    return $result;
}

function doQueryListeMissionsSoutPasSaisies($conn,$formation,$yearRef=null)
{
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString="select '11ereVisiteEntreprise' as etape,
                          concat (concat(tut.prenom,' '),tut.nom) as nomTut,
                          tut.mail as tutMail,
                          concat(concat(etud.nom,' '),etud.prenom) as nomEtud,
                          etud.mail as etudMail,
                          eg.groupeRef as niveau
                  from contrat inner join etudiant etud on etudCle=contrat.etudRef
                       inner join membre tut on profCle=tuteurRef
                       inner join etudiant_groupe eg on eg.etudRef=contrat.etudRef and annee=anneeCle
                        inner join groupe groupe on groupeRef=groupeCle
                  where alternanceCle not in (select alternanceRef from etapemissionsout
                                              where dateRencontre is not NULL) ".
                       "and groupeRef not like 'M1MIAGEFA%' and
                        '".$formation."' like concat('%',formationRef,'%')
                        and annee=$yearRef
              order by nomTut,niveau,nomEtud";

    $result=mysql_query($queryString,$conn);
    if (mysql_errno()!=0) error_log( "querying ",$queryString," - ",mysql_error());
    return $result;
}


function doQueryListeRencontresEtudiantPasSaisies($conn,$formation,$yearRef=null)
{
    if ($yearRef==null) $yearRef=$_SESSION[REF_YEAR];
    $queryString="select '1rencontreEtudiant' as etape,
                          concat (concat(tut.prenom,' '),tut.nom) as nomTut,
                          tut.mail as tutMail,
                          concat(concat(etud.nom,' '),etud.prenom) as nomEtud,
                          etud.mail as etudMail,
                          eg.groupeRef as niveau
                  from contrat inner join etudiant etud on etudCle=contrat.etudRef
                       inner join membre tut on profCle=tuteurRef
                       inner join etudiant_groupe eg on eg.etudRef=contrat.etudRef and annee=anneeCle
                       inner join groupe groupe on groupeRef=groupeCle
                  where alternanceCle not in (select alternanceRef from etapeetudtut
                                              where dateRencontre is not NULL) and
                        '".$formation."' like concat('%',formationRef,'%')
                        and annee=$yearRef
               order by nomTut,niveau,nomEtud";

    $result=mysql_query($queryString,$conn);
    if (mysql_errno()!=0) error_log( "querying ",$queryString," - ",mysql_error());
    return $result;
}
?>
